IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[cms_sp_PageSelectOne]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[cms_sp_PageSelectOne]
GO
create procedure [dbo].[cms_sp_PageSelectOne]
 (
 @PageId int, 
 @PublicPageId int = null,
 @SiteGroupId int,
 @SiteId int,
 @SystemLanguageId int = null,
 @PageTypeParameter nvarchar(400),
 @PageControlTypeParameter nvarchar(150),
 @IsInAdministration bit = null
 )
 as
 declare @TemplateId int
 declare @IsPublished bit
 declare @IsLatestRevision bit
 declare @IsLatestCompletedRevision bit
 declare @WorkflowId uniqueidentifier
 declare @PublishStartDate datetime
 declare @PublishEndDate datetime
 if @IsInAdministration is null
 begin
       set @IsInAdministration = 0
 end
 if @PublicPageId is not null
 begin
      declare @newPageId int;
      select 
              @newPageId = Pages.PageId
       from [dbo].[Pages]
       inner join [dbo].[EntityRevisions]
              on EntityRevisions.EntityId = Pages.PageId and EntityRevisions.EntityTypeParameter = 'Page'
       where (([SiteGroupId] is null and [SiteId] is null) or 
              ([SiteGroupId] = @SiteGroupId and [SiteId] is null) or
              ([SiteGroupId] = @SiteGroupId and [SiteId] = @SiteId)) and 
              ((@IsInAdministration = 1 and EntityRevisions.IsLatestRevision = 1) or
                     (@IsInAdministration = 0 and EntityRevisions.IsLatestCompletedRevision = 1)) and
              ((@IsInAdministration = 1) or
              (@IsInAdministration = 0 and EntityRevisions.IsPublished = 1)) and
              EntityRevisions.IsDeleted = 0 and
              Pages.PublicPageId = @PublicPageId
            
       set @PageId = @newPageId
end
 select 
       @TemplateId = Pages.TemplateId,
       @PublishStartDate = EntityRevisions.PublishStartDate,
       @PublishEndDate = EntityRevisions.PublishEndDate,
       @IsLatestRevision = EntityRevisions.IsLatestRevision,
       @IsLatestCompletedRevision = EntityRevisions.IsLatestCompletedRevision,
       @WorkflowId = EntityRevisions.WorkflowId,
       @IsPublished = EntityRevisions.IsPublished
 from [dbo].[Pages]
 inner join [dbo].[EntityRevisions]
       on EntityRevisions.EntityId = Pages.PageId and EntityRevisions.EntityTypeParameter = @PageTypeParameter
 where (([SiteGroupId] is null and [SiteId] is null) or 
       ([SiteGroupId] = @SiteGroupId and [SiteId] is null) or
       ([SiteGroupId] = @SiteGroupId and [SiteId] = @SiteId)) and     
       EntityRevisions.IsDeleted = 0 and
       Pages.PageId = @PageId
 select 
       Pages.PageId,
       Pages.PublicPageId,
       (select 
              ChildPages.PageId
       from dbo.Pages as ChildPages
       inner join dbo.EntityRevisions
       on EntityRevisions.EntityId = ChildPages.PageId and EntityRevisions.EntityTypeParameter = @PageTypeParameter and
              ((@IsInAdministration = 1 and EntityRevisions.IsLatestRevision = 1) or
              (@IsInAdministration = 0 and EntityRevisions.IsLatestCompletedRevision = 1))
       where ChildPages.PageNodeId = Pages.PageNodeId.GetAncestor(1) and
              ((ChildPages.[SiteGroupId] is null and ChildPages.[SiteId] is null) or 
              (ChildPages.[SiteGroupId] = @SiteGroupId and ChildPages.[SiteId] is null) or
              (ChildPages.[SiteGroupId] = @SiteGroupId and ChildPages.[SiteId] = @SiteId))) as ParentPageId,
       (select 
              ChildPages.PageName
       from dbo.Pages as ChildPages
       inner join dbo.EntityRevisions
       on EntityRevisions.EntityId = ChildPages.PageId and EntityRevisions.EntityTypeParameter = @PageTypeParameter and
              ((@IsInAdministration = 1 and EntityRevisions.IsLatestRevision = 1) or
              (@IsInAdministration = 0 and EntityRevisions.IsLatestCompletedRevision = 1))
       where ChildPages.PageNodeId = Pages.PageNodeId.GetAncestor(1) and
              ((ChildPages.[SiteGroupId] is null and ChildPages.[SiteId] is null) or 
              (ChildPages.[SiteGroupId] = @SiteGroupId and ChildPages.[SiteId] is null) or
              (ChildPages.[SiteGroupId] = @SiteGroupId and ChildPages.[SiteId] = @SiteId))) as ParentPageName,
       Pages.SiteGroupId,
       Pages.SiteId,
       Pages.SystemLanguageId,   
       Pages.PageTypeKey,
       Pages.PageBehaviourTypeKey,
       Pages.PageNodeId.GetLevel() as PageLevel,
       Pages.PageName,
       Pages.PageUrl,
       Pages.IsVisible,
       Pages.UseFileCache,
       Pages.PageTitle,
       Pages.PageTitleOverride,
       Pages.PageDescription,
       Pages.PageKeywords,
       Pages.CreatedBy,
       Pages.DateCreated,
       Pages.LastUpdatedBy,
       Pages.DateLastUpdated,
       @IsLatestRevision as IsLatestRevision,
       @IsLatestCompletedRevision as IsLatestCompletedRevision,
       @WorkflowId as WorkflowId,
       @IsPublished as IsPublished,
       @PublishStartDate as PublishStartDate,
       @PublishEndDate as PublishEndDate,
       Templates.TemplateId,
       Templates.TemplateName,
       Templates.TemplateTypeKey,
       Templates.TemplateUrl
 from dbo.Pages
 inner join dbo.Templates
       on Templates.TemplateId = Pages.TemplateId
 where Pages.PageId = @PageId and
       ((Pages.SiteGroupId is null and Pages.SiteId is null) or 
       (Pages.SiteGroupId = @SiteGroupId and Pages.SiteId is null) or
       (Pages.SiteGroupId = @SiteGroupId and Pages.SiteId = @SiteId))
 select
       @PageId as PageId,
       TemplateRegions.TemplateRegionId,
       TemplateRegions.TemplateRegionName,
       @TemplateId as TemplateId,
       InnerPageControls.PageControlId,
       InnerPageControls.ModuleId,
       InnerPageControls.PageControlTypeKey,
       InnerPageControls.PageControlViewValue,
       InnerPageControls.PageControlModelValue,
       InnerPageControls.PageControlContentId,
       InnerPageControls.PageControlOrder,
       InnerPageControls.PageControlContent,
       InnerPageControls.AdminCaption
 from dbo.TemplateRegions
 left join 
       (select             
              PageControls.PageControlId,
              PageControls.PageId,
              PageControls.TemplateRegionId,
              PageControls.ModuleId,
              PageControls.PageControlTypeKey,
              PageControls.PageControlViewValue,
              PageControls.PageControlModelValue,
              PageControls.PageControlContentId,
              PageControls.PageControlOrder,
              PageControls.AdminCaption,
              (select                   
                     Contents.ContentBody 
              from dbo.Contents
              where Contents.ContentId = PageControls.PageControlContentId
              ) as PageControlContent
       from dbo.PageControls
       inner join dbo.EntityRevisions
              on EntityRevisions.EntityId = PageControls.PageControlId and 
              EntityRevisions.EntityTypeParameter = @PageControlTypeParameter
       where ((@IsInAdministration = 1 and EntityRevisions.IsLatestRevision = 1) or
              (@IsInAdministration = 0 and EntityRevisions.IsLatestCompletedRevision = 1)) and
              ((@IsInAdministration = 1) or
              (@IsInAdministration = 0 and EntityRevisions.IsPublished = 1)) and
              EntityRevisions.IsDeleted = 0) as InnerPageControls
       on InnerPageControls.PageId = @PageId and InnerPageControls.TemplateRegionId = TemplateRegions.TemplateRegionId
 where TemplateRegions.TemplateId = @TemplateId 
 order by TemplateRegions.TemplateRegionId, InnerPageControls.PageControlOrder

GO

